package test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class Export {

    /**
     * @param args
     * @throws SQLException 
     * @throws IOException 
     * @throws ClassNotFoundException 
     */
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Class.forName("net.sourceforge.jtds.jdbc.Driver"); 
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost/TDFKFA", "sa", "123654");
        String sql = "select * from dbo.TDFKFA_MAIN";
        Statement st = con.createStatement();
        ResultSet resultSet = st.executeQuery(sql);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("abc");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        ResultSetMetaData md = resultSet.getMetaData();
        int nColumn = md.getColumnCount();
        // 写入各个字段的名称
        for (int i = 1; i <= nColumn; i++) {
            cell = row.createCell(i - 1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString(md.getColumnLabel(i)));
        }
        int iRow = 1;
        // 写入各条记录，每条记录对应Excel中的一行
        
        HSSFCellStyle style = wb.createCellStyle();
        style.setFillBackgroundColor(HSSFColor.BLUE.index);
        while (resultSet.next()) {
            row = sheet.createRow((short) iRow);
            for (int j = 1; j <= nColumn; j++) {
                cell = row.createCell(j - 1);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new HSSFRichTextString(resultSet.getObject(j) != null ? resultSet.getObject(j).toString() : ""));
            }
            iRow++;
        }
        FileOutputStream out = new FileOutputStream(new File("C:/a.xls"));
        wb.write(out);
        out.close();
    }
}
